iT邦幫忙

2025 iThome 鐵人賽

DAY 2
0
自我挑戰組

從「通靈系統」到穩健架構的契機系列 第 3

[Day 3] 資料表結構混亂:一張表看出專案的未來

  • 分享至 

  • xImage
  •  

https://ithelp.ithome.com.tw/upload/images/20250815/20178162OVBTcpm900.png

暫停在這,先看一眼Table,你能理解每個欄位的含意嗎?

你能一眼看懂下列欄位嗎?
ITEM_IDITEM_NOITEM_VENDERMAX_USE_COUNTCUR_USE_COUNTCUR_WOCUR_MOLD_NO
我第一眼看到時:尛,怎麼全部大寫,為什麼要縮寫

一眼難懂的欄位命名 — 欄位命名讓人懷疑人生

欄位 問題 解法
ITEM_ID 語意模糊,無法辨識是「料件」、「設備」還是「工單」 明確命名:EquipmentIdToolingId
ITEM_VENDER 拼字錯誤(Vender 應為 Vendor),ORM 映射會尷尬 改欄位名稱或在 ORM 先 mapping 成別名
CUR_* CUR 縮寫不明(Current?Currency?Stephen Curry?) 全名化:CurrentWorkOrderNoCurrentMoldNo
MAX_USE_COUNT / CUR_USE_COUNT 定義模糊,不知是「可用上限」vs「目前使用次數」還是「歷史最大值」 明確定義

資料型別與結構設計缺陷 — 型別與結構崩壞篇

欄位/結構 問題 解法
PK decimal(18,0) PK,風險包含撞號與交易鎖等待 改用 INT/BIGINT IDENTITY 當叢集鍵,GUID 作唯一非叢集索引(Clustered 與 PK 可分離,詳見後文)
萬能 nvarchar(255) 問題不大,但是無法反映實際長度需求,浪費儲存空間和 I/O 依需求調整:NVARCHAR(40)NVARCHAR(100)
STATUS_SID + STATUS 既然有 STATUS_SID ,應該有正規化,幹嘛存STATUS留下有機會不同步的風險 保留SID(FK),名稱 JOIN 字典表取得

效能與安全風險 — 沒索引就想飛?

問題 風險 解法
欄位過長 + 缺索引 查詢慢、全表掃描 根據查詢模式建立適當索引,並調整欄位長度
文本欄位隨意使用 容易發生 XSS、資料污染 前後端皆需輸入驗證、長度限制與內容過濾
PK 設計不合理 並發寫入易失敗、索引碎片化 採用合理 PK 型別與生成策略,降低鎖爭與 Page Split

補充風險與細節

1. 欄位過長 + 缺索引

  • NVARCHAR(255) / NVARCHAR(MAX) 會放大索引頁,降低 page 密度,I/O 開銷變大
  • 沒索引的常用欄位只能全表掃描,效能直接暴死
  • SARGable 原則:避免欄位運算、隱性轉型(例如 VARCHARNVARCHAR 混用)

改進方向

  1. 按實際需求縮短欄位長度(如客戶編號 NVARCHAR(40)
  2. 為查詢條件加 Nonclustered Index
  3. 確保查詢條件能直接命中索引

2. 文本欄位隨意使用

  • 安全風險:惡意輸入 <script>alert(1)</script> 直接進資料庫並渲染
  • 資料污染:儲存無效字元(ASCII < 32),比對異常難 debug
  • 跨系統污染:髒資料透過 API/ETL 傳染其他系統

改進方向

  • 前端:maxlength、pattern、格式驗證
  • 後端:長度/格式檢查、HTML Encode、白名單過濾(HtmlSanitizer)
  • DB:限制長度、過濾控制字元

3. PK 設計不合理 (這段很硬核)

  • 使用 decimal(18,0) 作為叢集鍵(Clustered PK)
    https://ithelp.ithome.com.tw/upload/images/20250815/20178162mWygZc16up.png

    • 鍵值過大 → 索引層級加深

      • decimal(18,0) 單鍵約 9 bytesint4 bytesbigint8 bytes

      • 以 8KB 資料頁為例(可用約 8096 bytes),索引內部節點粗略估算:

        • int:鍵(4) + 指標(8) ≈ 12 bytes → 一頁可放 ~674 個鍵。

        • decimal(18,0):鍵(9) + 指標(8) ≈ 17 bytes → 一頁僅 ~476 個鍵。

      • 結果:同樣 100 萬筆資料,decimal 的 B-Tree 層級較深、遍歷頁數更多,搜尋與範圍掃描 I/O 增加。

    • 非遞增插入 → Page Split(頁分裂)與碎片化

      • 具體例子:某頁(Page #123)已依鍵排序存放 100000000000000001 ~ 100000000000000100 共 100 筆。

      • 新插入鍵 100000000000000050中間位置),該頁已滿 → Page Split

        1. 分配新頁(Page #456)

        2. 搬 Page #123 後半段(約 50 筆)到 #456

        3. 把新資料插回正確頁面

        4. 更新父節點指標(索引重連結)

      • 代價:8KB 頁搬運 + 大量 Transaction Log;若插入長期非末尾(例如應用程式計算、批次回填),碎片化比例可飆 >30%,範圍查詢明顯變慢。

    • 放大所有非叢集索引(NCI)成本

      • 在 SQL Server,NCI 葉節點會附帶從叢集鍵作為 Row Locator。

      • 叢集鍵若是 decimal(18,0)(9 bytes),所有 NCI 都會跟著變胖,百萬筆時索引體積、記憶體占用、I/O 全面上升。

    • 結論/建議

      • 不要用 decimal(18,0) 做叢集鍵

      • 最佳解int identity/bigint identityClustered PK(單調遞增、減少分裂)。

      • 若不想改 decimal ,改建 Nonclustered Unique Index 維持唯一性,不要拿它當叢集鍵。

      • 需要對外不可猜的 ID → 另加 PUBLIC_ID UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID(),建立 Unique NCI 對外用,內部仍以遞增鍵為主。

改善建議
採用 INT SEQNO 遞增 Clustered Index + Guid ID 非叢集唯一索引,同時兼顧效能與安全性。


推薦設計:SeqNo + Guid 雙鍵架構

(參考:Darkthread〈GUID 當 PK 的取捨〉

設計架構

CREATE TABLE dbo.Tooling (
    SeqNo INT IDENTITY(1,1) NOT NULL,
    Id UNIQUEIDENTIFIER NOT NULL 
       CONSTRAINT DF_Tooling_Id DEFAULT NEWSEQUENTIALID(),
       
    ToolingNo NVARCHAR(40) NOT NULL,
    Vendor NVARCHAR(100) NULL,
    -- ...
    CONSTRAINT PK_Tooling_Id PRIMARY KEY NONCLUSTERED (Id)  -- PK = Id(非叢集)
);
GO
CREATE CLUSTERED INDEX CX_Tooling_SeqNo ON dbo.Tooling(SeqNo); -- 叢集在 SeqNo(遞增)

優勢分析

1. 效能優化

  • SeqNo(INT/BIGINT Identity、Clustered Index)

    • 遞增數值 → 插入落在最後一頁,降低 Page Split

    • 窄鍵INT/BIGINT)讓 B-Tree 更淺,提升查詢/排序效能,B-Tree 與鍵大小掛鉤

    • 分頁、排序、批次處理都吃得到好處。

  • Id(GUID、Nonclustered Index / PK Nonclustered)

    • 對外唯一識別碼,適合 API 曝光,避免順序 ID 被推測

    • 非叢集設計避免 GUID 當 clustered 的碎片化災難

B-Tree 與鍵大小掛鉤 範例對比

型別 鍵大小 指標大小 一頁可放鍵數 影響
INT 4 B 8 B ~674 樹最淺
BIGINT 8 B 8 B ~506 稍深
DECIMAL(18,0) 9 B 8 B ~476 更深
GUID 16 B 8 B ~347 最深

查詢路徑示意(為什麼查 GUID 看起來會用 SeqNo?)

WHERE Id = @guid
   │
   ├─ Index Seek on (Id)        ──>  先在 Id 的 NCI 找到這列的叢集鍵「SeqNo」
   │
   └─ Key Lookup (Clustered on SeqNo)  ──>  用 SeqNo 回到 Clustered 取完整那一列

2. 安全性提升

  • API 對外回傳 Guid,難以暴力猜測。

  • 多資料中心或離線批次產號時,Guid 保證全域唯一性。


3. 可維護性

  • ORM 可直接映射 Id(Guid)作為識別碼,SeqNo 作為內部排序與關聯用。

  • 避免像 ITEM_ID 這種模糊命名,改用明確名詞(ToolingIdEquipmentId)。


4. 資料整合性

  • 合併多系統資料時,Guid 避免撞號。

  • SeqNo 保留本地資料寫入的高效能。


總結

這張表的特徵是:

命名靠猜、型別不佳、效能看緣分,最嚴重的是 PK 設計不合理,所以我多著墨了一點

所以以後我自己會盡量保持:

  • 一眼讓人看懂的命名
  • 可預期的型別與鍵設計(Clustered 與 PK 解耦)
  • 以查詢模式為中心的索引
  • 把資料品質跟安全擋在第一線

上一篇
[Day 2] 魔法字串的代價比你想的高
系列文
從「通靈系統」到穩健架構的契機3
圖片
  熱門推薦
圖片
{{ item.channelVendor }} | {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言